package com.zlj.jdbc.dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.zlj.jdbc.jdbcUtils02.JdbcUtils;

/**
 * commons-dbutils 是为了简化jdbc代码！
 * @author bashen
 *
 */
public class Demo01 {
	@Test
	public void addEmployee(Employee employee) {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = JdbcUtils.getConnection();
			System.out.println(conn);
			String sql = "insert into employee values(?,?,?,?)";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, employee.getId());
			statement.setString(2, employee.getName());
			statement.setString(3, employee.getEmail());
			statement.setString(4, employee.getGender());
			statement.executeUpdate();
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if(statement != null) statement.close();
				if(conn != null) conn.close();
			} catch(Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}
	
	@Test
	public void updateEmployee(Employee employee) {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = JdbcUtils.getConnection();
			System.out.println(conn);
			String sql = "update employee set name = ?, email = ?, gender = ? where id = ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, employee.getName());
			statement.setString(2, employee.getEmail());
			statement.setString(3, employee.getGender());
			statement.setInt(4, employee.getId());
			statement.executeUpdate();
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if(statement != null) statement.close();
				if(conn != null) conn.close();
			} catch(Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}
	
	@Test
	public void delEmployee(int id) {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = JdbcUtils.getConnection();
			System.out.println(conn);
			String sql = "delete from employee where id = ?";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if(statement != null) statement.close();
				if(conn != null) conn.close();
			} catch(Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}
	
	@Test
	public Employee queryEmp(int id){
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			conn = JdbcUtils.getConnection();
			System.out.println(conn);
			String sql = "select * from employee where id = ?";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			if(!resultSet.next()) return null;
			
			/**
			 * 需要把resultSet 转换为Employee对象
			 * resultSet  ---> javaBean
			 */
			Employee employee = new Employee();
			employee.setName(resultSet.getString("name"));
			employee.setEmail(resultSet.getString("email"));
			employee.setGender(resultSet.getString("gender"));
			return employee;
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if(resultSet != null) resultSet.close();
				if(statement != null) statement.close();
				if(conn != null) conn.close();
			} catch(Exception e1) {
				throw new RuntimeException(e1);
			}
		}
	}
}
